﻿using QG.FrontDesk.Core;
using QG.FrontDesk.DAL.IRepository;
using QG.FrontDesk.Model;
using System.Collections.Generic;

namespace QG.FrontDesk.DAL.Repository
{
    public class ResumeRepository : IResumeRepository
    {
        /// <summary>
        /// 得到所有的简历信息
        /// </summary>
        /// <returns></returns>
        public List<ResumeInfo> GetResumeInfos()
        {
            string sql = @"select * from resumeinfo";
            List<ResumeInfo> resumeInfos = DapperDBHelper<ResumeInfo>.QueryList(sql, null);
            foreach (var item in resumeInfos)
            {
                //得到简历下的工作经历
                sql = @"SELECT * FROM resumeInfo
                        INNER JOIN businessExperien ON businessExperien.`experId`= resumeInfo.`experId`
                        where resumeInfo.`experId`=@experId";
                item.Businessexperien = DapperDBHelper<Businessexperien>.QueryFirstOrDefault(sql, new { experId = item.ExperId });
                //得到简历下的教育背景
                sql = @"SELECT * FROM resumeInfo
                    INNER JOIN educational ON educational.`educId`= resumeInfo.`educId`
                    WHERE educational.educId = @educId";
                item.Educational = DapperDBHelper<Educational>.QueryFirstOrDefault(sql, new { educId = item.EducId });
                //得到简历下的工作性质
                sql = @"SELECT * FROM resumeInfo
                        INNER JOIN ExpectToWork ON expecttowork.`workId`= resumeInfo.`workId`
                        where resumeInfo.`workId`=@workId";
                item.Expecttowork = DapperDBHelper<ExpecttoWork>.QueryFirstOrDefault(sql, new { workId = item.WorkId });
                //得到简历对应的用户名
                sql = @"SELECT * FROM resumeInfo
                    INNER JOIN userInfo ON userInfo.`userId`= resumeInfo.`userId`
                    WHERE resumeInfo.`userId`= @userId";
                item.UserInfo = DapperDBHelper<UserInfo>.QueryFirstOrDefault(sql, new { userId = item.UserId });
            }
            return resumeInfos;
        }

        /// <summary>
        /// 期望工作——获取期望城市
        /// </summary>
        /// <returns></returns>
        public List<Cityinfo> GetCityinfos()
        {
            string sql = "SELECT * FROM cityinfo";
            return DapperDBHelper<Cityinfo>.QueryList(sql, null);
        }

        /// <summary>
        /// 获取简历信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public ResumeInfo GetResumeInfo(int id)
        {
            //简历信息表
            string sql = @"SELECT * FROM resumeInfo where UserId = @id";

            ResumeInfo resumeInfo = DapperDBHelper<ResumeInfo>.QueryFirstOrDefault(sql, new { id = id })??new ResumeInfo();

            //获取对应的用户信息表信息
            sql = @"SELECT * FROM resumeInfo
                    INNER JOIN userInfo ON resumeInfo.`userId` = userInfo.`userId`
                    WHERE rId = @Rid";
            resumeInfo.UserInfo = DapperDBHelper<UserInfo>.QueryFirstOrDefault(sql, new { Rid = resumeInfo.RId })??new UserInfo();

            //获取对应的期望工作表信息
            sql = @"SELECT * FROM resumeInfo
                    INNER JOIN expectToWork ON resumeInfo.`workId` = expectToWork.`workId`
                    INNER JOIN cityinfo ON cityinfo.cityId=expecttowork.`cityId`
                    WHERE rId = @Rid";
            resumeInfo.Expecttowork = DapperDBHelper<ExpecttoWork>.QueryFirstOrDefault(sql, new { Rid = resumeInfo.RId })??new ExpecttoWork();

            //获取对应的工作经历表信息
            sql = @"SELECT * FROM resumeInfo
                    INNER JOIN businessExperien ON resumeInfo.`experId`= businessExperien.`experId`
                    WHERE rId = @Rid";
            resumeInfo.Businessexperien = DapperDBHelper<Businessexperien>.QueryFirstOrDefault(sql, new { Rid = resumeInfo.RId })??new Businessexperien();

            //获取对应的项目经验表信息
            sql = @"SELECT * FROM resumeInfo
                    INNER JOIN projectExperien ON resumeInfo.`proId`= projectExperien.`proId`
                    WHERE rId = @Rid";
            resumeInfo.ProjectExperien = DapperDBHelper<ProjectExperien>.QueryFirstOrDefault(sql, new { Rid = resumeInfo.RId })??new ProjectExperien();

            //获取对应的教育背景表信息
            sql = @"SELECT * FROM resumeInfo
                    INNER JOIN educational ON resumeinfo.`educId` = educational.`educId`
                    WHERE rId = @Rid";
            resumeInfo.Educational = DapperDBHelper<Educational>.QueryFirstOrDefault(sql, new { Rid = resumeInfo.RId })??new Educational();

            //获取对应的作品展示表信息
            sql = @"SELECT * FROM resumeInfo
                    INNER JOIN worksShow ON resumeInfo.`showId` = worksShow.`showId`
                    WHERE rId = @Rid";
            resumeInfo.WorksShow = DapperDBHelper<WorksShow>.QueryFirstOrDefault(sql, new { Rid = resumeInfo.RId })??new WorksShow();

            return resumeInfo;
        }

        /// <summary>
        /// 修改“用户信息表”
        /// </summary>
        /// <param name="userInfo"></param>
        /// <returns></returns>
        public int UpdateUserInfo(UserInfo userInfo)
        {
            string sql = @"UPDATE userInfo SET 
                           `userName`=@UserName,
                           `sex`=@Sex,
                           `phone`=@Phone,
                           `email`=@Email,
                           `status`=@Status,
                           `touXiang`=@TouXiang
                           WHERE userId = @UserId";
            return DapperDBHelper<UserInfo>.Execute(sql, new
            {
                userName = userInfo.UserName,
                sex = userInfo.Sex,               
                phone = userInfo.Phone,
                email = userInfo.Email,
                status = userInfo.Status,
                touXiang = userInfo.TouXiang,
                userId = userInfo.UserId
            });
        }

        /// <summary>
        /// 修改“期望工作表”
        /// </summary>
        /// <param name="expecttoWork"></param>
        /// <returns></returns>
        public int UpdateExpectToWork(ExpecttoWork expecttoWork)
        {
            string sql = @"UPDATE expectToWork SET 
                          `cityId`=@CityId,
                          `workNature`=@WorkNature,
                          `ponsitDesired`=@PonsitDesired,
                          `expectSalary`=@ExpectSalary 
                           WHERE workId = @WorkId";
            return DapperDBHelper<ExpecttoWork>.Execute(sql, new
            {
                cityId = expecttoWork.CityId,
                workNature = expecttoWork.WorkNature,
                ponsitDesired = expecttoWork.PonsitDesired,
                expectSalary = expecttoWork.ExpectSalary,
                workId = expecttoWork.WorkId
            });
        }

        /// <summary>
        /// 修改“工作经历表”
        /// </summary>
        /// <param name="businessexperien"></param>
        /// <returns></returns>
        public int UpdateBusinessexperien(Businessexperien businessexperien)
        {
            string sql = @"UPDATE businessexperien SET 
                          `companyName`=@CompanyName,
                          `desiredName`=@DesiredName,
                          `beginTime`=@BeginTime,
                          `overTime`=@OverTime 
                           WHERE experId = @ExperId";
            return DapperDBHelper<Businessexperien>.Execute(sql, new
            {
                companyName = businessexperien.CompanyName,
                desiredName = businessexperien.DesiredName,
                beginTime = businessexperien.BeginTime,
                overTime = businessexperien.OverTime,
                experId = businessexperien.ExperId
            });
        }

        /// <summary>
        /// 修改“项目经验表”
        /// </summary>
        /// <param name="projectExperien"></param>
        /// <returns></returns>
        public int UpdateProjectExperien(ProjectExperien projectExperien)
        {
            string sql = @"UPDATE projectExperien SET
                          `projectName`=@ProjectName,
                          `holdPost`=@HoldPost,
                          `beginTime`=@BeginTime,
                          `overTime`=@OverTime,
                          `projectDescribe`=@ProjectDescribe 
                           WHERE proId = @ProId";
            return DapperDBHelper<ProjectExperien>.Execute(sql, new
            {
                projectName = projectExperien.ProjectName,
                holdPost = projectExperien.HoldPost,
                beginTime = projectExperien.BeginTime,
                overTime = projectExperien.OverTime,
                projectDescribe = projectExperien.ProjectDescribe,
                proId = projectExperien.ProId
            });
        }

        /// <summary>
        /// 修改“教育背景表”
        /// </summary>
        /// <param name="educational"></param>
        /// <returns></returns>
        public int UpdateEducation(Educational educational)
        {
            string sql = @"UPDATE educational SET
                           `schoolName`=@SchoolName,
                           `education`=@Education,
                           `majorName`=@MajorName,
                           `beginTime`=@BeginTime,
                           `overTime`=@OverTime 
                           WHERE educId = @EducId";
            return DapperDBHelper<Educational>.Execute(sql, new
            {
                schoolName = educational.SchoolName,
                education = educational.Education,
                majorName = educational.MajorName,
                beginTime = educational.BeginTime,
                overTime = educational.OverTime,
                educId = educational.EducId
            });
        }

        /// <summary>
        /// 修改“作品展示表”
        /// </summary>
        /// <param name="worksShow">作品展示表</param>
        /// <returns></returns>
        public int UpdateWorksShow(WorksShow worksShow)
        {
            string sql = @"UPDATE worksShow SET
                           `workLink`=@WorkLink,
                           `remark`=@Remark 
                           WHERE showId = @ShowId";
            return DapperDBHelper<WorksShow>.Execute(sql, new
            {
                workLink = worksShow.WorkLink,
                remark = worksShow.Remark,
                showId = worksShow.ShowId
            });
        }

        /// <summary>
        /// 根据id 查询工作经历表
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Businessexperien GetBusinessexperien(int id)
        {
            string sql = @"SELECT * FROM businessexperien WHERE experId = @ExperId";
            return DapperDBHelper<Businessexperien>.QueryFirstOrDefault(sql, new { experId = id });
        }

        /// <summary>
        /// 新增简历附件（文件上传）
        /// </summary>
        /// <param name="resumeInfo"></param>
        /// <returns></returns>
        public int AddResumePath(ResumeInfo resumeInfo)
        {
            string sql = @"UPDATE resumeInfo SET resumePath = @resumePath WHERE rid=@rid";
            return DapperDBHelper<ResumeInfo>.Execute(sql, new { resumePath = resumeInfo.ResumePath, rid = resumeInfo.RId });
        }


    }
}
